<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/11/28
 * Time: 16:19
 */
namespace app\common\api;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Excel{
    /**
     * Notes: excel表格导出
     * User: 王俊成
     * Date: 2018/10/29
     * Time: 10:14
     * @param $expFileName
     * @param $remarkData
     * @param $expCellName
     * @param $expTableData
     */
    public function exportExcel($expFileName,$remarkData,$expCellName,$expTableData){
        $xlsFileName = iconv('utf-8', 'gb2312', $expFileName);//文件名称
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        $spreadsheetObj = new Spreadsheet();
        $spreadsheetObj->setActiveSheetIndex(0);//设置激活的表
        $sheet = $spreadsheetObj->getActiveSheet();
        $cellName = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'];

        $spreadsheetObj->getDefaultStyle()->getAlignment()->setHorizontal('center');//设置所有格居中显示
        // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expFileName.'  Export time:'.date('Y-m-d H:i:s'));
        $count = 0;
        //设置备注
        if(isset($remarkData) && is_array($remarkData)){ // 设置备注
            foreach($remarkData as $value){
                $count++;
                $sheet->setCellValue('A'.$count, $value);
            }
            $count = 0;
            foreach($remarkData as $value){ // 合并备注单元格
                $count ++;
                $mergeCell = $cellName[0].$count.':'.$cellName[$cellNum-1].$count;
                $sheet->mergeCells($mergeCell);
            }
        }
        $count++;
        //设置列格式和列名
        for($i=0;$i<$cellNum;$i++){
            //$sheet->getColumnDimension($cellName[$i])->setAutoSize(true);//设置单元格自动宽度
            $sheet->setCellValue($cellName[$i].$count, $expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        //设置数据
        $count++;
        for($i = 0;$i < $dataNum;$i++){
            for($j = 0;$j < $cellNum;$j++){
//            $sheet->setCellValue($cellName[$j].($i+$count), $expTableData[$i][$expCellName[$j][0]]."\t");//加\t是强制转换数据类型为文本
//			  $sheet->setCellValueExplicit($cellName[$j].($i+$count), $expTableData[$i][$expCellName[$j][0]],\PHPExcel_Cell_DataType::TYPE_NUMERIC);//转换为指定类型
                $sheet->setCellValue($cellName[$j].($i+$count), $expTableData[$i][$expCellName[$j][0]]);
            }
        }

        ob_end_clean();//清楚缓存区，解决乱码问题
        header('pragma:public');
        //其实IE是根据Content-Disposition中filename这个段中文件名的后缀来识别这个文件类型的，所以，如果有很多种文件类型的时候，可以将Content-Type设置为二进制模式的：Header("Content-type:   application/octet-stream");  vnd.ms-excel
        header('Content-type:application/octet-stream;charset=utf-8;name="'.$xlsFileName.'.xlsx"');
        header("Content-Disposition:inline;filename=$xlsFileName.xlsx");//attachment新窗口打印inline本窗口打印
        $writerObj = new Xlsx($spreadsheetObj);
        $writerObj->save('php://output');
        //断开与表之间的连接
        $spreadsheetObj->disconnectWorksheets();
        unset($spreadsheetObj);
        exit;
    }
}
